﻿IF EXISTS (
    SELECT
      1
    FROM sysobjects
    WHERE id = OBJECT_ID('bd_proc_schedule_group_schedule_save')
      AND type IN ('P', 'PC')
  )
  DROP PROCEDURE bd_proc_schedule_group_schedule_save

GO

-- =============================================
-- Author:      YFC
-- Create date: 2020-07-21 09:21:40
-- Database:    [scm_main-hysj2]
-- Description: 排班管理-班组排班保存
-- =============================================

CREATE PROCEDURE bd_proc_schedule_group_schedule_save
(
  @groupId INT,
  @shiftNo  VARCHAR(50),
  @shiftTimeBegin DATE,
  @shiftTimeEnd DATE,
  @shiftType  INT,
  @userIds  VARCHAR(MAX),
  @ip VARCHAR(20),
  @operateId  BIGINT
)
AS
BEGIN
  SET NOCOUNT ON;


  CREATE TABLE #return_table
  (
    user_serial VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
    user_no VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
    user_name VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
    dept_name VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
    error_msg VARCHAR(200) COLLATE Chinese_PRC_CI_AS
  )

  DECLARE @userIdList TABLE
  (
  	user_serial INT
  )

  IF ISNULL(@userIds, '') = '' 
  BEGIN  
    INSERT INTO @userIdList (user_serial)
  	SELECT user_serial FROM bd_schedule_group_employee WHERE group_id = @groupId

    DECLARE @tmp VARCHAR(MAX) = ''
    SELECT @tmp = @tmp + CAST(user_serial AS VARCHAR(20)) + ',' FROM @userIdList
    SET @userIds = SUBSTRING(@tmp, 1, LEN(@tmp) - 1)
  END
  ELSE
  BEGIN
    INSERT INTO @userIdList (user_serial)
  	SELECT value FROM STRING_SPLIT(@userIds, ',')
  END
  
  --班次ID
  DECLARE @shiftId  INT




--  IF EXISTS (
--      SELECT
--        1
--      FROM #return_table
--    )
--  BEGIN
--    RAISERROR ('排班出错', 16, 1);
--    RETURN
--  END

  BEGIN TRY
    BEGIN TRANSACTION

      IF @shiftType = 1 
      BEGIN  
        --普通排班
        SELECT @shiftId = id FROM zt_banci WHERE bh = @shiftNo
    
        EXEC bd_proc_schedule_pu_tong @userIds, @shiftTimeBegin, @shiftTimeEnd, @shiftNo, @ip, @operateId
      END
      ELSE
      BEGIN
      	--规律班次排班
        SET @shiftId = CAST(@shiftNo AS INT)
    
        EXEC bd_proc_schedule_gui_lv @userIds, @shiftTimeBegin, @shiftTimeEnd, @shiftNo, @ip, @operateId
      END

      INSERT INTO bd_schedule_group_order (group_id, shift_id, shift_time_begin, shift_time_end, shift_type, create_id, create_time)
      VALUES(@groupId, @shiftId, @shiftTimeBegin, @shiftTimeEnd, @shiftType, @operateId, GETDATE())

      --生成日期
      DECLARE @date TABLE
      (
      	date_day DATE
      )
      
      DECLARE @dateFrom DATETIME = @shiftTimeBegin
      DECLARE @dateTo DATETIME = @shiftTimeEnd
      ;WITH tmpDays AS              
       (              
        SELECT @dateFrom date_value            
        UNION ALL             
        SELECT date_value + 1             
        FROM    tmpDays            
        WHERE   date_value + 1 <= @dateTo             
       )
      INSERT INTO @date (date_day)
      SELECT CONVERT(VARCHAR(10), date_value, 120) FROM tmpDays OPTION (MAXRECURSION 0);


      DECLARE @schedule TABLE
      (
        group_id  INT,
      	user_serial BIGINT,
      	shedule_date  DATE
      )
      
      INSERT INTO @schedule (group_id, user_serial, shedule_date)
      SELECT @groupId group_id, user_serial, date_day FROM @userIdList, @date


      MERGE
      INTO bd_schedule_group_order_detail p
      USING @schedule s ON p.group_id = s.group_id AND p.user_serial = s.user_serial AND p.schedule_date = s.shedule_date
      WHEN NOT MATCHED BY TARGET
      THEN INSERT(group_id, user_serial, schedule_date)
        VALUES(group_id, user_serial, shedule_date)
      ;
        
      SELECT 1

    COMMIT TRANSACTION
  END TRY
  BEGIN CATCH
    DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();

    IF XACT_STATE() <> 0
    BEGIN
      ROLLBACK TRANSACTION;
    END;
    RAISERROR (@errStr, 16, 1);
  END CATCH;


  DROP TABLE #return_table

  SET NOCOUNT OFF;
END
GO
